package com.blunka.framework.client.db.druid.service;

import java.io.File;
import java.io.FileInputStream;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.blunka.framework.client.constants.InitDesignConfig;
import com.blunka.framework.client.db.druid.dbutils.IServiceDbHelper;
import com.blunka.framework.client.db.druid.dbutils.ServerDbHelper;
import com.blunka.framework.client.db.entity.BatchSqlEntity;
import com.blunka.framework.client.enums.ModelField;
import com.blunka.framework.client.enums.ModelStates;
import com.blunka.framework.client.enums.ModelStatus;
import com.blunka.framework.client.enums.ModelType;
import com.blunka.framework.client.ui.entity.BlkTableMetaData;
import com.blunka.framework.client.utils.DesignUtils;


/**
 *
 * @平台：bronca统一开发平台
 * @文件名称：MySqlServerDbHelper.java
 * @文件描述：druid数据库连接工具mysql
 * @author xuqiang
 * @日期：2017年5月2日上午8:53:25
 *
 */
public class MySqlServer implements ISQLService{

	private static final Logger logger = Logger.getLogger(MySqlServer.class);

	/**
	 * 数据库服务类
	 */
	public static IServiceDbHelper serviceDbHelper;

	private static MySqlServer dbHelper = new MySqlServer();

	private static Properties p;

	public static MySqlServer newInstance(){
		return dbHelper;
	}

	private MySqlServer(){
		try{
			p = new Properties();
			File pfile = new File(DesignUtils.getDir()+"/conf/conf-mysql-db.properties");
			p.load(new FileInputStream(pfile));
			serviceDbHelper = new ServerDbHelper();
			serviceDbHelper.initDataSource(p);
			logger.info("初始化数据库成功");
		}catch(Exception e){
			logger.error("数据库连接失败",e);
		}
	}

	/**
	 * 获取业务建模数据版本
	 * @return
	 */
	public String getDBVersion(){
		Map<String,String> versionMap = dbHelper.getDBUtils().getItemMap("SELECT * FROM BLK_TABLE_VERSION");
		return versionMap.get("DB_VERSION");
	}

	/**
	 * 获取数据库地址
	 * @return
	 */
	public String getDBServerIP(){
		String dbUrl = p.getProperty("url");
		if(StringUtils.isEmpty(dbUrl)){
			return "";
		}
		String ip = "";
		Matcher m = Pattern.compile("((\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})\\:\\d{1,5})").matcher(dbUrl);
		while (m.find()) {
			ip = m.group(1);
			break;
        }
		return ip.substring(0, ip.lastIndexOf(":"));
	}
	
	/**
	 * 获取数据库名称
	 * @return
	 */
	public String getDBServerName() {
		String dbUrl = p.getProperty("url");
		if(StringUtils.isEmpty(dbUrl)){
			return "";
		}
		dbUrl = dbUrl.substring(0, dbUrl.indexOf("?"));
		String db_name = dbUrl.substring(dbUrl.lastIndexOf("/")+1);
		return db_name;
	}
	
	/**
	 * 获取数据库操作工具
	 * @return
	 */
	public IServiceDbHelper getDBUtils(){
		return serviceDbHelper;
	}

	/**
	 * 初始化远程数据库
	 * @throws Exception
	 */
	public void initServerDB()throws Exception{
		//删除当前数据库所有表和视图结构
		StringBuffer drop_table_sql = new StringBuffer("SELECT CONCAT('DROP TABLE IF EXISTS ',TABLE_NAME,';') AS EXECUTE_SQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+getDBServerName().toUpperCase()+"' AND TABLE_TYPE='BASE TABLE'")
				.append(" UNION ALL ")
				.append("SELECT CONCAT('DROP VIEW IF EXISTS ',TABLE_NAME,';')AS EXECUTE_SQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+getDBServerName().toUpperCase()+"' AND TABLE_TYPE='VIEW'");
		List<Map<String,String>> table_sql_list = dbHelper.getDBUtils().getMapList(drop_table_sql.toString());
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		for(Map<String,String> sql_map : table_sql_list) {
			BatchSqlEntity sqlEntity = new BatchSqlEntity();
			sqlEntity.setSql(sql_map.get("EXECUTE_SQL"));

			executeSql.add(sqlEntity);
		}
		dbHelper.getDBUtils().batch(executeSql);
		
		//重新初始化
		List<String> sqlList = InitDesignConfig.getServerDBSql();
		executeSql.clear();
		//List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		for(String sql : sqlList){
			BatchSqlEntity sqlEntity = new BatchSqlEntity();
			sqlEntity.setSql(sql);

			executeSql.add(sqlEntity);
		}
		dbHelper.getDBUtils().batch(executeSql);
	}

	/**
	 * 查询数据结合
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<BlkTableMetaData> queryAllNode() throws Exception {
		List<BlkTableMetaData> list = dbHelper.getDBUtils().getBeanList("SELECT * FROM BLK_TABLE_METADATA WHERE MODEL_STATES !='4' ORDER BY ORDER_BY ASC", BlkTableMetaData.class);
		return list;
	}
	
	/**
	 * 插入新节点，并且返回插入的节点内容
	 * @param node
	 * @return
	 */
	@Override
	public BlkTableMetaData insertNode(BlkTableMetaData node) throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//先删除
		StringBuffer delSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA where TABLEMETADATA_ID=?");
		Object delParams[] = {node.getTablemetadata_id()};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(delSql.toString());
		entity.setParams(delParams);
		executeSql.add(entity);

		//在插入
		StringBuffer insertSql = new StringBuffer("INSERT INTO BLK_TABLE_METADATA(TABLEMETADATA_ID,FATHER_TABLEMETADATA_ID,ZH_CN,EN_US,DESCRIBES,OBJ_PATH,MODEL_STATES,MODEL_TYPE,VIEW_SQL,ORDER_BY) VALUES(?,?,?,?,?,?,?,?,?,?)");
		Object insertParams[] = {node.getTablemetadata_id(),
				node.getFather_tablemetadata_id(),
				node.getZh_cn(),
				node.getEn_us(),
				node.getDescribes(),
				node.getObj_path(),
				node.getModel_states().getIndex(),
				node.getModel_type().getIndex(),
				node.getView_sql(),
				node.getOrder_by()
				};
		BatchSqlEntity entity2 = new BatchSqlEntity();
		entity2.setSql(insertSql.toString());
		entity2.setParams(insertParams);
		executeSql.add(entity2);
		dbHelper.getDBUtils().batch(executeSql);
		return node;
	}
	
	/**
	 * 插入新节点，并且返回插入的节点内容
	 * @param node
	 * @return
	 */
	@Override
	public BlkTableMetaData insertNodeNoNewId(BlkTableMetaData node)throws Exception{
		return insertNode(node);
	}
	
	

	/**
	 * 更新节点
	 * @param node
	 * @return
	 * @throws Exception
	 */
	@Override
	public BlkTableMetaData updateNode(BlkTableMetaData node) throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		StringBuffer sql = new StringBuffer("UPDATE BLK_TABLE_METADATA SET FATHER_TABLEMETADATA_ID=?,ZH_CN=?,EN_US=?,DESCRIBES=?,OBJ_PATH=?,MODEL_STATES=?,MODEL_TYPE=?,VIEW_SQL=?,ORDER_BY=? WHERE TABLEMETADATA_ID=?");
		Object params[] = {
				node.getFather_tablemetadata_id(),
				node.getZh_cn(),
				node.getEn_us(),
				node.getDescribes(),
				node.getObj_path(),
				node.getModel_states().getIndex(),
				node.getModel_type().getIndex(),
				node.getView_sql(),
				node.getOrder_by(),
				node.getTablemetadata_id()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);
		dbHelper.getDBUtils().batch(executeSql);
		return node;
	}

	/**
	 * 删除节点
	 * @param node
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData deleteNode(BlkTableMetaData node) throws Exception{
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		StringBuffer sql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA  WHERE TABLEMETADATA_ID=?");
		Object params[] = {node.getTablemetadata_id()};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);
		dbHelper.getDBUtils().batch(executeSql);
		return node;
	}
	
	/**
	 * 新建表，只存储业务模型数据
	 * @param node
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData insertTableNodeModel(BlkTableMetaData node) throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//先删除
		StringBuffer delSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA where TABLEMETADATA_ID=?");
		Object delParams[] = {node.getTablemetadata_id()};
		BatchSqlEntity delEntity = new BatchSqlEntity();
		delEntity.setSql(delSql.toString());
		delEntity.setParams(delParams);
		executeSql.add(delEntity);

		//再插入
		StringBuffer sql = new StringBuffer("INSERT INTO BLK_TABLE_METADATA(TABLEMETADATA_ID,FATHER_TABLEMETADATA_ID,ZH_CN,EN_US,DESCRIBES,OBJ_PATH,MODEL_STATES,MODEL_TYPE,VIEW_SQL,ORDER_BY) VALUES(?,?,?,?,?,?,?,?,?,?)");
		Object params[] = {node.getTablemetadata_id(),
				node.getFather_tablemetadata_id(),
				node.getZh_cn(),
				node.getEn_us(),
				node.getDescribes(),
				node.getObj_path(),
				node.getModel_states().getIndex(),
				node.getModel_type().getIndex(),
				node.getView_sql(),
				node.getOrder_by()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);
		executeSql.add(entity);
		dbHelper.getDBUtils().batch(executeSql);
		
		//获取
		BlkTableMetaData newNode = dbHelper.getDBUtils().getBean("SELECT * FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+node.getTablemetadata_id()+"'", BlkTableMetaData.class);
		return newNode;
	}

	/**
	 * 新建表
	 * @param node
	 * @return
	 * @throws Exception
	 */
	@Override
	public BlkTableMetaData insertTableNode(BlkTableMetaData node) throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//先删除
		StringBuffer delSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA where TABLEMETADATA_ID=?");
		Object delParams[] = {node.getTablemetadata_id()};
		BatchSqlEntity delEntity = new BatchSqlEntity();
		delEntity.setSql(delSql.toString());
		delEntity.setParams(delParams);
		executeSql.add(delEntity);

		//再插入
		StringBuffer sql = new StringBuffer("INSERT INTO BLK_TABLE_METADATA(TABLEMETADATA_ID,FATHER_TABLEMETADATA_ID,ZH_CN,EN_US,DESCRIBES,OBJ_PATH,MODEL_STATES,MODEL_TYPE,VIEW_SQL,ORDER_BY) VALUES(?,?,?,?,?,?,?,?,?,?)");
		Object params[] = {node.getTablemetadata_id(),
				node.getFather_tablemetadata_id(),
				node.getZh_cn(),
				node.getEn_us(),
				node.getDescribes(),
				node.getObj_path(),
				node.getModel_states().getIndex(),
				node.getModel_type().getIndex(),
				node.getView_sql(),
				node.getOrder_by()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);

		if(node.getModel_type() == ModelType.TABLE){
			//建表
			String create_sql = InitDesignConfig.getServerDBSqlRoot().element("createtable").elementText("sqltext");
			create_sql = MessageFormat.format(create_sql, new Object[]{node.getEn_us()});
			BatchSqlEntity sqlEntity = new BatchSqlEntity();
			sqlEntity.setSql(create_sql);
			sqlEntity.setParams(null);
			executeSql.add(sqlEntity);
		}else if(node.getModel_type() == ModelType.VIEW){
			//视图
			if(!StringUtils.isEmpty(node.getView_sql())){
				StringBuffer view_sql = new StringBuffer("CREATE OR REPLACE VIEW ").append(node.getEn_us()).append(" AS ").append(node.getView_sql());
				BatchSqlEntity sqlEntity = new BatchSqlEntity();
				sqlEntity.setSql(view_sql.toString());
				sqlEntity.setParams(null);
				executeSql.add(sqlEntity);
			}
		}
		dbHelper.getDBUtils().batch(executeSql);
		return node;
	}
	
	/**
	 * 新建表
	 * @param node
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData insertTableNodeNoNewId(BlkTableMetaData node)throws Exception{
		return insertTableNode(node);
	}
	
	/**
	 * 更新表，只更新业务数据
	 * @param node
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData updateTableNodeModel(BlkTableMetaData node)throws Exception{ 
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		StringBuffer sql = new StringBuffer("UPDATE BLK_TABLE_METADATA SET FATHER_TABLEMETADATA_ID=?,ZH_CN=?,EN_US=?,DESCRIBES=?,OBJ_PATH=?,MODEL_STATES=?,MODEL_TYPE=?,VIEW_SQL=?,ORDER_BY=? WHERE TABLEMETADATA_ID=?");
		Object params[] = {
				node.getFather_tablemetadata_id(),
				node.getZh_cn(),
				node.getEn_us(),
				node.getDescribes(),
				node.getObj_path(),
				node.getModel_states().getIndex(),
				node.getModel_type().getIndex(),
				node.getView_sql(),
				node.getOrder_by(),
				node.getTablemetadata_id()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);
		dbHelper.getDBUtils().batch(executeSql);

		return node;
	}

	/**
	 * 更新表
	 * @param node
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData updateTableNode(BlkTableMetaData node)throws Exception{
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		StringBuffer sql = new StringBuffer("UPDATE BLK_TABLE_METADATA SET FATHER_TABLEMETADATA_ID=?,ZH_CN=?,EN_US=?,DESCRIBES=?,OBJ_PATH=?,MODEL_STATES=?,MODEL_TYPE=?,VIEW_SQL=?,ORDER_BY=? WHERE TABLEMETADATA_ID=?");
		Object params[] = {
				node.getFather_tablemetadata_id(),
				node.getZh_cn(),
				node.getEn_us(),
				node.getDescribes(),
				node.getObj_path(),
				node.getModel_states().getIndex(),
				node.getModel_type().getIndex(),
				node.getView_sql(),
				node.getOrder_by(),
				node.getTablemetadata_id()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);

		if(node.getModel_type() == ModelType.TABLE){
			//查询原表判断是否一致
			BlkTableMetaData oldTableMetaData = dbHelper.getDBUtils().getBean("SELECT * FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+node.getTablemetadata_id()+"'", BlkTableMetaData.class);
			if(!oldTableMetaData.getEn_us().equals(node.getEn_us())){
				String update_table_sql = "ALTER  TABLE "+oldTableMetaData.getEn_us()+" RENAME TO "+node.getEn_us();
				BatchSqlEntity sqlEntity = new BatchSqlEntity();
				sqlEntity.setSql(update_table_sql);
				sqlEntity.setParams(null);
				executeSql.add(sqlEntity);
			}
		}else if(node.getModel_type() == ModelType.VIEW){
			//视图通过create or replace可以直接替换

			if(!StringUtils.isEmpty(node.getView_sql())){
				StringBuffer view_sql = new StringBuffer("CREATE OR REPLACE VIEW ").append(node.getEn_us()).append(" AS ").append(node.getView_sql());
				BatchSqlEntity sqlEntity = new BatchSqlEntity();
				sqlEntity.setSql(view_sql.toString());
				sqlEntity.setParams(null);
				executeSql.add(sqlEntity);
			}
		}
		dbHelper.getDBUtils().batch(executeSql);

		return node;
	}
	
	/**
	 * 删除表,只删除业务数据
	 * @param node
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData	deleteTableNodeModel(BlkTableMetaData node)throws Exception{
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//删除业务模型数据结构和表所对应的字段业务模型数据
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql("DELETE FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+node.getTablemetadata_id()+"' or FATHER_TABLEMETADATA_ID='"+node.getTablemetadata_id()+"'");
		entity.setParams(null);
		executeSql.add(entity);
		dbHelper.getDBUtils().batch(executeSql);
		return node;
	}
	/**
	 * 删除表
	 * @param node
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData	deleteTableNode(BlkTableMetaData node)throws Exception{
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		if(node.getModel_type() == ModelType.TABLE){
			//删除表
			BatchSqlEntity entity = new BatchSqlEntity();
			entity.setSql("DROP TABLE "+node.getEn_us());
			entity.setParams(null);
			executeSql.add(entity);
		}else if(node.getModel_type() == ModelType.VIEW){
			//删除视图
			BatchSqlEntity entity = new BatchSqlEntity();
			entity.setSql("DROP VIEW IF EXISTS "+node.getEn_us());
			entity.setParams(null);
			executeSql.add(entity);
		}
		//删除业务模型数据结构和表所对应的字段业务模型数据
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql("DELETE FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+node.getTablemetadata_id()+"' or FATHER_TABLEMETADATA_ID='"+node.getTablemetadata_id()+"'");
		entity.setParams(null);
		executeSql.add(entity);



		dbHelper.getDBUtils().batch(executeSql);
		return node;
	}
	
	/**
	 * 插入字段,只插入业务数据
	 * @param node 新节点
	 * @param table 所属表名
	 * @return
	 * @throws Exception
	 */
	@Override
	public BlkTableMetaData insertFieldModel(BlkTableMetaData newBlkModel, BlkTableMetaData parentBlkModel)
			throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//先删除
		StringBuffer delSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA where TABLEMETADATA_ID=?");
		Object delParams[] = {newBlkModel.getTablemetadata_id()};
		BatchSqlEntity delEntity = new BatchSqlEntity();
		delEntity.setSql(delSql.toString());
		delEntity.setParams(delParams);
		executeSql.add(delEntity);

		//插入
		StringBuffer sql = new StringBuffer("INSERT INTO BLK_TABLE_METADATA(TABLEMETADATA_ID,FATHER_TABLEMETADATA_ID,ZH_CN,EN_US,DESCRIBES,OBJ_PATH,MODEL_STATES,MODEL_TYPE,MODEL_FIELD,MODEL_FIELD_LEN,IS_PRIMARY_KEY,IS_EMPTY,SHOW_WIDTH,IS_HIDDEN,IS_VIEWER,IS_READER,IS_DISABLED,ERROR_MSG,INPUT_PROMIT,GROUP_TITLE,USER_CONFIG,SEL_OPTIONS,VIEW_SQL,ORDER_BY) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		Object params[] = {newBlkModel.getTablemetadata_id(),
				newBlkModel.getFather_tablemetadata_id(),
				newBlkModel.getZh_cn(),
				newBlkModel.getEn_us(),
				newBlkModel.getDescribes(),
				newBlkModel.getObj_path(),
				newBlkModel.getModel_states().getIndex(),
				newBlkModel.getModel_type().getIndex(),
				newBlkModel.getModel_field().getIndex(),
				newBlkModel.getModel_field_len(),
				String.valueOf(newBlkModel.getIs_primary_key().getIndex()),
				String.valueOf(newBlkModel.getIs_empty().getIndex()),
				newBlkModel.getShow_width(),
				String.valueOf(newBlkModel.getIs_hidden().getIndex()),
				String.valueOf(newBlkModel.getIs_viewer().getIndex()),
				String.valueOf(newBlkModel.getIs_reader().getIndex()),
				String.valueOf(newBlkModel.getIs_disabled().getIndex()),
				newBlkModel.getError_msg(),
				newBlkModel.getInput_promit(),
				newBlkModel.getGroup_title(),
				newBlkModel.getUser_config(),
				newBlkModel.getSel_options(),
				newBlkModel.getView_sql(),
				newBlkModel.getOrder_by()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);
		dbHelper.getDBUtils().batch(executeSql);
		
		//获取
		BlkTableMetaData newNode = dbHelper.getDBUtils().getBean("SELECT * FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+newBlkModel.getTablemetadata_id()+"'", BlkTableMetaData.class);
		return newNode;
	}

	/**
	 * 插入字段
	 * @param node 新节点
	 * @param table 所属表名
	 * @return
	 * @throws Exception
	 */
	@Override
	public BlkTableMetaData insertField(BlkTableMetaData newBlkModel, BlkTableMetaData parentBlkModel)
			throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//先删除
		StringBuffer delSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA where TABLEMETADATA_ID=?");
		Object delParams[] = {newBlkModel.getTablemetadata_id()};
		BatchSqlEntity delEntity = new BatchSqlEntity();
		delEntity.setSql(delSql.toString());
		delEntity.setParams(delParams);
		executeSql.add(delEntity);

		//插入
		StringBuffer sql = new StringBuffer("INSERT INTO BLK_TABLE_METADATA(TABLEMETADATA_ID,FATHER_TABLEMETADATA_ID,ZH_CN,EN_US,DESCRIBES,OBJ_PATH,MODEL_STATES,MODEL_TYPE,MODEL_FIELD,MODEL_FIELD_LEN,IS_PRIMARY_KEY,IS_EMPTY,SHOW_WIDTH,IS_HIDDEN,IS_VIEWER,IS_READER,IS_DISABLED,ERROR_MSG,INPUT_PROMIT,GROUP_TITLE,USER_CONFIG,SEL_OPTIONS,VIEW_SQL,ORDER_BY) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		Object params[] = {newBlkModel.getTablemetadata_id(),
				newBlkModel.getFather_tablemetadata_id(),
				newBlkModel.getZh_cn(),
				newBlkModel.getEn_us(),
				newBlkModel.getDescribes(),
				newBlkModel.getObj_path(),
				newBlkModel.getModel_states().getIndex(),
				newBlkModel.getModel_type().getIndex(),
				newBlkModel.getModel_field().getIndex(),
				newBlkModel.getModel_field_len(),
				String.valueOf(newBlkModel.getIs_primary_key().getIndex()),
				String.valueOf(newBlkModel.getIs_empty().getIndex()),
				newBlkModel.getShow_width(),
				String.valueOf(newBlkModel.getIs_hidden().getIndex()),
				String.valueOf(newBlkModel.getIs_viewer().getIndex()),
				String.valueOf(newBlkModel.getIs_reader().getIndex()),
				String.valueOf(newBlkModel.getIs_disabled().getIndex()),
				newBlkModel.getError_msg(),
				newBlkModel.getInput_promit(),
				newBlkModel.getGroup_title(),
				newBlkModel.getUser_config(),
				newBlkModel.getSel_options(),
				newBlkModel.getView_sql(),
				newBlkModel.getOrder_by()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);

		if(parentBlkModel.getModel_type() == ModelType.TABLE){
			StringBuffer alterSql = new StringBuffer("ALTER TABLE ").append(parentBlkModel.getEn_us()).append(" ADD ").append(newBlkModel.getEn_us());
			if(newBlkModel.getModel_field() == ModelField.DATE){
				alterSql.append(" DATETIME ");
			}else if(newBlkModel.getModel_field() == ModelField.CLOB){
				alterSql.append(" TEXT ");
			}else if(newBlkModel.getModel_field() == ModelField.BLOB){
				alterSql.append(" BLOB ");
			}else if(newBlkModel.getModel_field() == ModelField.NUMBER){
				alterSql.append(" INT ");
			}else if(newBlkModel.getModel_field() == ModelField.FLOAT){
				alterSql.append(" FLOAT ");
			}else{
				alterSql.append(" VARCHAR(").append(newBlkModel.getModel_field_len()).append(") ");
			}
			if(newBlkModel.getIs_empty() == ModelStatus.NO){
				alterSql.append(" NOT NULL ");
			}
			if(newBlkModel.getIs_primary_key() == ModelStatus.YES){
				alterSql.append(" UNIQUE ");
			}
			BatchSqlEntity en = new BatchSqlEntity();
			en.setSql(alterSql.toString());
			en.setParams(null);
			executeSql.add(en);
		}
		dbHelper.getDBUtils().batch(executeSql);
		return newBlkModel;
	}
	
	@Override
	public BlkTableMetaData insertField(BlkTableMetaData newBlkModel)
			throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//先删除
		StringBuffer delSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA where TABLEMETADATA_ID=?");
		Object delParams[] = {newBlkModel.getTablemetadata_id()};
		BatchSqlEntity delEntity = new BatchSqlEntity();
		delEntity.setSql(delSql.toString());
		delEntity.setParams(delParams);
		executeSql.add(delEntity);

		//插入
		StringBuffer sql = new StringBuffer("INSERT INTO BLK_TABLE_METADATA(TABLEMETADATA_ID,FATHER_TABLEMETADATA_ID,ZH_CN,EN_US,DESCRIBES,OBJ_PATH,MODEL_STATES,MODEL_TYPE,MODEL_FIELD,MODEL_FIELD_LEN,IS_PRIMARY_KEY,IS_EMPTY,SHOW_WIDTH,IS_HIDDEN,IS_VIEWER,IS_READER,IS_DISABLED,ERROR_MSG,INPUT_PROMIT,GROUP_TITLE,USER_CONFIG,SEL_OPTIONS,VIEW_SQL,ORDER_BY) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		Object params[] = {newBlkModel.getTablemetadata_id(),
				newBlkModel.getFather_tablemetadata_id(),
				newBlkModel.getZh_cn(),
				newBlkModel.getEn_us(),
				newBlkModel.getDescribes(),
				newBlkModel.getObj_path(),
				newBlkModel.getModel_states().getIndex(),
				newBlkModel.getModel_type().getIndex(),
				newBlkModel.getModel_field().getIndex(),
				newBlkModel.getModel_field_len(),
				String.valueOf(newBlkModel.getIs_primary_key().getIndex()),
				String.valueOf(newBlkModel.getIs_empty().getIndex()),
				newBlkModel.getShow_width(),
				String.valueOf(newBlkModel.getIs_hidden().getIndex()),
				String.valueOf(newBlkModel.getIs_viewer().getIndex()),
				String.valueOf(newBlkModel.getIs_reader().getIndex()),
				String.valueOf(newBlkModel.getIs_disabled().getIndex()),
				newBlkModel.getError_msg(),
				newBlkModel.getInput_promit(),
				newBlkModel.getGroup_title(),
				newBlkModel.getUser_config(),
				newBlkModel.getSel_options(),
				newBlkModel.getView_sql(),
				newBlkModel.getOrder_by()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);

		BlkTableMetaData parentBlkModel = dbHelper.getDBUtils().getBean("SELECT * FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+newBlkModel.getTablemetadata_id()+"'", BlkTableMetaData.class);
		if(parentBlkModel.getModel_type() == ModelType.TABLE){
			StringBuffer alterSql = new StringBuffer("ALTER TABLE ").append(parentBlkModel.getEn_us()).append(" ADD ").append(newBlkModel.getEn_us());
			if(newBlkModel.getModel_field() == ModelField.DATE){
				alterSql.append(" DATETIME ");
			}else if(newBlkModel.getModel_field() == ModelField.CLOB){
				alterSql.append(" TEXT ");
			}else if(newBlkModel.getModel_field() == ModelField.BLOB){
				alterSql.append(" BLOB ");
			}else if(newBlkModel.getModel_field() == ModelField.NUMBER){
				alterSql.append(" INT ");
			}else if(newBlkModel.getModel_field() == ModelField.FLOAT){
				alterSql.append(" FLOAT ");
			}else{
				alterSql.append(" VARCHAR(").append(newBlkModel.getModel_field_len()).append(") ");
			}
			if(newBlkModel.getIs_empty() == ModelStatus.NO){
				alterSql.append(" NOT NULL ");
			}
			if(newBlkModel.getIs_primary_key() == ModelStatus.YES){
				alterSql.append(" UNIQUE ");
			}
			BatchSqlEntity en = new BatchSqlEntity();
			en.setSql(alterSql.toString());
			en.setParams(null);
			executeSql.add(en);
		}
		dbHelper.getDBUtils().batch(executeSql);
		return newBlkModel;
	}
	
	/**
	 * 插入字段,直插入业务数据
	 * @param newBlkModel 新字段
	 * @param oldBlkModel 旧字段
	 * @param parentBlkModel 父节点
	 * @return
	 * @throws Exception
	 */
	@Override
	public BlkTableMetaData updateFieldModel(BlkTableMetaData newBlkModel,BlkTableMetaData parentBlkModel) throws Exception{
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//查询老版本
		//BlkTableMetaData oldBlkModel = dbHelper.getDBUtils().getBean("SELECT * FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+newBlkModel.getTablemetadata_id()+"'", BlkTableMetaData.class);

		StringBuffer sql = new StringBuffer("UPDATE BLK_TABLE_METADATA SET FATHER_TABLEMETADATA_ID=?,ZH_CN=?,EN_US=?,DESCRIBES=?,OBJ_PATH=?,MODEL_STATES=?,MODEL_TYPE=?,MODEL_FIELD=?,MODEL_FIELD_LEN=?,IS_PRIMARY_KEY=?,IS_EMPTY=?,SHOW_WIDTH=?,IS_HIDDEN=?,IS_VIEWER=?,IS_READER=?,IS_DISABLED=?,ERROR_MSG=?,INPUT_PROMIT=?,GROUP_TITLE=?,USER_CONFIG=?,SEL_OPTIONS=?,VIEW_SQL=?,ORDER_BY=? WHERE TABLEMETADATA_ID=?");
		Object params[] = {newBlkModel.getFather_tablemetadata_id(),
				newBlkModel.getZh_cn(),
				newBlkModel.getEn_us(),
				newBlkModel.getDescribes(),
				newBlkModel.getObj_path(),
				newBlkModel.getModel_states().getIndex(),
				newBlkModel.getModel_type().getIndex(),
				newBlkModel.getModel_field().getIndex(),
				newBlkModel.getModel_field_len(),
				String.valueOf(newBlkModel.getIs_primary_key().getIndex()),
				String.valueOf(newBlkModel.getIs_empty().getIndex()),
				newBlkModel.getShow_width(),
				String.valueOf(newBlkModel.getIs_hidden().getIndex()),
				String.valueOf(newBlkModel.getIs_viewer().getIndex()),
				String.valueOf(newBlkModel.getIs_reader().getIndex()),
				String.valueOf(newBlkModel.getIs_disabled().getIndex()),
				newBlkModel.getError_msg(),
				newBlkModel.getInput_promit(),
				newBlkModel.getGroup_title(),
				newBlkModel.getUser_config(),
				newBlkModel.getSel_options(),
				newBlkModel.getView_sql(),
				newBlkModel.getOrder_by(),
				newBlkModel.getTablemetadata_id()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);
		dbHelper.getDBUtils().batch(executeSql);
		return newBlkModel;
	}

	/**
	 * 插入字段
	 * @param newBlkModel 新字段
	 * @param oldBlkModel 旧字段
	 * @param parentBlkModel 父节点
	 * @return
	 * @throws Exception
	 */
	@Override
	public BlkTableMetaData updateField(BlkTableMetaData newBlkModel,BlkTableMetaData parentBlkModel) throws Exception {
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		//查询老版本
		BlkTableMetaData oldBlkModel = dbHelper.getDBUtils().getBean("SELECT * FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='"+newBlkModel.getTablemetadata_id()+"'", BlkTableMetaData.class);

		StringBuffer sql = new StringBuffer("UPDATE BLK_TABLE_METADATA SET FATHER_TABLEMETADATA_ID=?,ZH_CN=?,EN_US=?,DESCRIBES=?,OBJ_PATH=?,MODEL_STATES=?,MODEL_TYPE=?,MODEL_FIELD=?,MODEL_FIELD_LEN=?,IS_PRIMARY_KEY=?,IS_EMPTY=?,SHOW_WIDTH=?,IS_HIDDEN=?,IS_VIEWER=?,IS_READER=?,IS_DISABLED=?,ERROR_MSG=?,INPUT_PROMIT=?,GROUP_TITLE=?,USER_CONFIG=?,SEL_OPTIONS=?,VIEW_SQL=?,ORDER_BY=? WHERE TABLEMETADATA_ID=?");
		Object params[] = {newBlkModel.getFather_tablemetadata_id(),
				newBlkModel.getZh_cn(),
				newBlkModel.getEn_us(),
				newBlkModel.getDescribes(),
				newBlkModel.getObj_path(),
				newBlkModel.getModel_states().getIndex(),
				newBlkModel.getModel_type().getIndex(),
				newBlkModel.getModel_field().getIndex(),
				newBlkModel.getModel_field_len(),
				String.valueOf(newBlkModel.getIs_primary_key().getIndex()),
				String.valueOf(newBlkModel.getIs_empty().getIndex()),
				newBlkModel.getShow_width(),
				String.valueOf(newBlkModel.getIs_hidden().getIndex()),
				String.valueOf(newBlkModel.getIs_viewer().getIndex()),
				String.valueOf(newBlkModel.getIs_reader().getIndex()),
				String.valueOf(newBlkModel.getIs_disabled().getIndex()),
				newBlkModel.getError_msg(),
				newBlkModel.getInput_promit(),
				newBlkModel.getGroup_title(),
				newBlkModel.getUser_config(),
				newBlkModel.getSel_options(),
				newBlkModel.getView_sql(),
				newBlkModel.getOrder_by(),
				newBlkModel.getTablemetadata_id()
				};
		BatchSqlEntity entity = new BatchSqlEntity();
		entity.setSql(sql.toString());
		entity.setParams(params);

		executeSql.add(entity);

		//表
		if(parentBlkModel.getModel_type() == ModelType.TABLE){
			//修改表字段名称
			StringBuffer alterSql = new StringBuffer("ALTER TABLE ").append(parentBlkModel.getEn_us()).append(" CHANGE ").append(oldBlkModel.getEn_us())
					.append(" ").append(newBlkModel.getEn_us());

			if(newBlkModel.getModel_field() == ModelField.DATE){
				alterSql.append(" DATETIME ");
			}else if(newBlkModel.getModel_field() == ModelField.CLOB){
				alterSql.append(" TEXT ");
			}else if(newBlkModel.getModel_field() == ModelField.BLOB){
				alterSql.append(" BLOB ");
			}else if(newBlkModel.getModel_field() == ModelField.NUMBER){
				alterSql.append(" INT ");
			}else if(newBlkModel.getModel_field() == ModelField.FLOAT){
				alterSql.append(" FLOAT ");
			}else{
				alterSql.append(" VARCHAR(").append(newBlkModel.getModel_field_len()).append(") ");
			}
			if(newBlkModel.getIs_empty() == ModelStatus.NO){
				alterSql.append(" NOT NULL ");
			}

			//新字段UNIQUE索引名称
			String table_index_name = newBlkModel.getEn_us();
			if(newBlkModel.getIs_primary_key() == ModelStatus.YES){
				if(!queryIndexExists(table_index_name,parentBlkModel.getEn_us())){
					alterSql.append(" UNIQUE ");
				}

			}
			BatchSqlEntity en = new BatchSqlEntity();
			en.setSql(alterSql.toString());
			en.setParams(null);
			executeSql.add(en);

			if(newBlkModel.getIs_primary_key() == ModelStatus.NO){
				//存在则删除
				if(queryIndexExists(table_index_name,parentBlkModel.getEn_us())){
					StringBuffer uniqueSql = new StringBuffer("ALTER TABLE ").append(parentBlkModel.getEn_us()).append("  DROP INDEX ").append(newBlkModel.getEn_us());
					BatchSqlEntity en2 = new BatchSqlEntity();
					en2.setSql(uniqueSql.toString());
					en2.setParams(null);
					executeSql.add(en2);
				}
			}
		}
		dbHelper.getDBUtils().batch(executeSql);
		return newBlkModel;
	}

	/**
	 * 删除字段,只删除业务数据
	 * @param newBlkModel
	 * @param parentBlkModel
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData deleteFieldModel(BlkTableMetaData newBlkModel,BlkTableMetaData parentBlkModel)throws Exception{
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		if(parentBlkModel !=null){
			//父节点为非删除状态，需要删除对应的字段业务模型
			if(parentBlkModel.getModel_states() != ModelStates.DELETE){
				StringBuffer alterSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='").append(newBlkModel.getTablemetadata_id()).append("'");

				BatchSqlEntity entity = new BatchSqlEntity();
				entity.setSql(alterSql.toString());
				entity.setParams(null);
				executeSql.add(entity);
			}

			dbHelper.getDBUtils().batch(executeSql);
		}

		return newBlkModel;
	}
	
	/**
	 * 删除字段
	 * @param newBlkModel
	 * @param parentBlkModel
	 * @return
	 * @throws Exception
	 */
	public BlkTableMetaData deleteField(BlkTableMetaData newBlkModel,BlkTableMetaData parentBlkModel)throws Exception{
		List<BatchSqlEntity> executeSql = new ArrayList<BatchSqlEntity>();
		if(parentBlkModel !=null){
			//字段所属表存在，可以删除表字段
			if(parentBlkModel.getModel_type() == ModelType.TABLE && parentBlkModel.getModel_states() != ModelStates.DELETE){
				//当前字段的父节点为表并且该表的状态为非删除状态，可以删除数据库表字段
				StringBuffer alterSql = new StringBuffer("ALTER TABLE ").append(parentBlkModel.getEn_us()).append(" DROP COLUMN ").append(newBlkModel.getEn_us());

				BatchSqlEntity entity = new BatchSqlEntity();
				entity.setSql(alterSql.toString());
				entity.setParams(null);
				executeSql.add(entity);
			}
			//父节点为非删除状态，需要删除对应的字段业务模型
			if(parentBlkModel.getModel_states() != ModelStates.DELETE){
				StringBuffer alterSql = new StringBuffer("DELETE FROM BLK_TABLE_METADATA WHERE TABLEMETADATA_ID='").append(newBlkModel.getTablemetadata_id()).append("'");

				BatchSqlEntity entity = new BatchSqlEntity();
				entity.setSql(alterSql.toString());
				entity.setParams(null);
				executeSql.add(entity);
			}

			dbHelper.getDBUtils().batch(executeSql);
		}

		return newBlkModel;
	}

	/**
	 * 查询表或者是否是否已经存在
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean queryTableOrViewExists(BlkTableMetaData model) throws SQLException {
		String url = p.getProperty("url");
		url = url.substring(0,url.indexOf("?"));
		String dbname = url.substring(url.lastIndexOf("/")+1);
		StringBuffer sql = new StringBuffer("SELECT TABLE_NAME FROM ( ");
		sql.append("SELECT TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='"+dbname+"' UNION ")
		.append("SELECT TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+dbname+"'")
		.append(")T");
		sql.append(" WHERE TABLE_NAME='").append(model.getEn_us()).append("'");
		List<Map<String,String>> list = dbHelper.getDBUtils().getMapList(sql.toString());
		if(list !=null && !list.isEmpty() && list.size()>0){
			return true;
		}else{
			return false;
		}
	}

	/**
	 * 查询表中的字段是否已经存在
	 * @param BlkTableMetaData model
	 * @param BlkTableMetaData tableModel 所属表
	 * @return
	 * @throws SQLException
	 */
	public boolean queryTableOrViewFieldExists(BlkTableMetaData model,String tablename)throws SQLException{
		String url = p.getProperty("url");
		url = url.substring(0,url.indexOf("?"));
		String dbname = url.substring(url.lastIndexOf("/")+1);
		StringBuffer sql = new StringBuffer("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='").append(tablename).append("' AND COLUMN_NAME='").append(model.getEn_us()).append("' AND TABLE_SCHEMA='").append(dbname).append("'");
		List<Map<String,String>> list = dbHelper.getDBUtils().getMapList(sql.toString());
		if(list !=null && !list.isEmpty() && list.size()>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 判断modelname业务模型名称在节点model的子节点中是否存在
	 * @param model 当前节点
	 * @param modelname 子节点名称
	 * @return
	 * @throws SQLException
	 */
	public boolean queryModelNodeExists(BlkTableMetaData model,String modelname)throws SQLException{
		String sql = "SELECT * FROM BLK_TABLE_METADATA WHERE FATHER_TABLEMETADATA_ID=(SELECT TABLEMETADATA_ID FROM BLK_TABLE_METADATA WHERE OBJ_PATH='"+model.getObj_path()+"') AND EN_US='"+modelname+"'";
		List<Map<String,String>> list = dbHelper.getDBUtils().getMapList(sql.toString());
		if(list !=null && !list.isEmpty() && list.size()>0){
			return true;
		}else{
			return false;
		}
	}

	/**
	 * 查询表字段索引是否存在
	 * @param table_fieldindex_name
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean queryIndexExists(String table_fieldindex_name,String tablename) throws SQLException {
		String url = p.getProperty("url");
		url = url.substring(0,url.indexOf("?"));
		String dbname = url.substring(url.lastIndexOf("/")+1);
		StringBuffer sql = new StringBuffer("SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='"+dbname+"' and CONSTRAINT_TYPE='unique' ")
				.append(" AND TABLE_NAME = '").append(tablename).append("' AND CONSTRAINT_NAME = '")
				.append(table_fieldindex_name).append("'");
		List<Map<String,String>> list = dbHelper.getDBUtils().getMapList(sql.toString());
		if(list !=null && !list.isEmpty() && list.size()>0){
			return true;
		}else{
			return false;
		}
	}

	/**
	 * 删除数据库选中的节点及子节点
	 * @param selModel
	 * @param parentModel
	 * @throws Exception
	 */
	@Override
	public void batchModelNode(List<BatchSqlEntity> sqlList) throws Exception {
		dbHelper.getDBUtils().batch(sqlList);
	}

	@Override
	public BatchSqlEntity getinsertNodeNoNewIdSql(BlkTableMetaData node) throws Exception {
		
		return null;
	}

	@Override
	public BatchSqlEntity getInsertTableNodeSql(BlkTableMetaData node) throws Exception {
		
		return null;
	}

	@Override
	public BatchSqlEntity getInsertFieldSql(BlkTableMetaData newBlkModel) throws Exception {
		
		return null;
	}

}
